import numpy as np
import pandas as pd

cater_file_path = '../../data/raw data/餐饮连锁数据.xlsx'
sheet_names=['门店信息','菜品信息','销售记录','顾客评价']
sheet_name=sheet_names[2]  # 销售记录表
df_sales = pd.read_excel(cater_file_path, sheet_name)

print("\n【空值统计】")
print(df_sales.isnull().sum())
print("======================")

# --- 测试1：查看哪些列空值最多 ---
missing_rate = df_sales.isnull().mean().sort_values(ascending=False)
print("\n【空值比例（Top 10）】")
print(missing_rate.head(10))
print("======================")

# --- 处理方式：删除含有空值的行 ---
before_rows = df_sales.shape[0]
df_sales.dropna(inplace=True)
after_rows = df_sales.shape[0]
print(f"\n【空值处理】已删除 {before_rows - after_rows} 行包含空值的数据。")

# --- 验证：是否还有空值 ---
print("\n【空值处理后验证】")
print(df_sales.isnull().sum().sum())
print('======================')
print('======================')

# ======================================
# 3️⃣ 检查并处理重复值
# ======================================
print("\n【重复值检测】")
print(df_sales.duplicated().sum())
print('================================')

if df_sales.duplicated().sum() > 0:
    print(df_sales[df_sales.duplicated()])

df_sales.drop_duplicates(inplace=True)

print("\n【重复值处理后验证】")
print(df_sales.duplicated().sum())
print('======================')
print('======================')
print('=============================')

# ======================================
# 4️⃣ 识别并修正异常值
# ======================================

print("\n【数值列统计描述】")
print(df_sales.describe())

print('日期--------------------------------------')
outer='日期'
if outer in df_sales.columns:
    # 定义异常值的阈值
    upper = pd.Timestamp('2025-11-02')
    lower = pd.Timestamp('2020-01-01')

    # 标记异常值
    outliers = df_sales[(df_sales[outer] > upper) | (df_sales[outer] < lower)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数
    median_value = df_sales[outer].median()
    df_sales.loc[(df_sales[outer] > upper) | (df_sales[outer] < lower), outer] = median_value
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")

print('时段--------------------------------------')
outer='时段'
if outer in df_sales.columns:
    # 定义正常时段格式（包含冒号和横杠的时间区间）
    valid_pattern = r'^\d{1,2}:\d{2}-\d{1,2}:\d{2}$'

    # 标记异常值
    outliers = df_sales[~df_sales[outer].astype(str).str.match(valid_pattern)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为众数
    mode_value = df_sales[outer].mode()[0]
    df_sales.loc[~df_sales[outer].astype(str).str.match(valid_pattern), outer] = mode_value
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为众数 {mode_value}。")

print('销售数量--------------------------------------')
outer='销售数量'
if outer in df_sales.columns:
    mean_value = df_sales[outer].mean()
    std_value = df_sales[outer].std()
    upper = mean_value + 3 * std_value
    lower = 1  # 最小销售数量为1

    # 标记异常值
    outliers = df_sales[(df_sales[outer] > upper) | (df_sales[outer] < lower) | (df_sales[outer] < 0)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数并取整
    median_value = int(round(df_sales[outer].median()))
    df_sales.loc[(df_sales[outer] > upper) | (df_sales[outer] < lower) | (df_sales[outer] < 0), outer] = median_value
    # 确保所有值为正整数
    df_sales[outer] = df_sales[outer].apply(lambda x: int(round(x)) if pd.notnull(x) else x)
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")

print('成本率--------------------------------------')
outer='成本率'
if outer in df_sales.columns:
    # 定义异常值的阈值
    upper = 1.0
    lower = 0.0

    # 标记异常值
    outliers = df_sales[(df_sales[outer] > upper) | (df_sales[outer] < lower) | (df_sales[outer] < 0)]
    print(f"\n检测到异常值数量：{len(outliers)}")

    # 替换异常值为中位数并保留六位小数
    median_value = round(df_sales[outer].median(), 6)
    df_sales.loc[(df_sales[outer] > upper) | (df_sales[outer] < lower) | (df_sales[outer] < 0), outer] = median_value
    # 确保所有值保留六位小数
    df_sales[outer] = df_sales[outer].round(6)
    print(f"\n【异常值处理】已将 {len(outliers)} 个异常值替换为中位数 {median_value}。")

print('======================================')
print('======================================')

# ======================================
# 6️⃣ 导出清洗后的数据
# ======================================
clean_path = '../../data/cleared data/餐饮连锁数据_销售记录2_cleaned.xlsx'
df_sales.to_excel(clean_path, index=False)
print(f"\n✅ 数据清洗完成，已保存至 {clean_path}")